package mysql

import (
	"fmt"
	"github.com/pkg/errors"
	"strings"
	"xorm.io/builder"
)

// 白名单防 sql 注入验证
func check(info *SqlInfo) (err error) {
	if len(info.Field) > 0 {
		field := strings.Join(info.Field, ",")
		if err = filterColumns([]string{field}); err != nil {
			return err
		}
		info.Field = []string{field}
	}
	if info.Order != "" {
		if err = filterColumns([]string{info.Order}); err != nil {
			return err
		}
	}
	if info.Group != "" {
		if err = filterColumns([]string{info.Group}); err != nil {
			return err
		}
	}
	return nil
}

// 查询类 SQL
func SelectSql(info *SqlInfo) (sql string, values []interface{}, err error) {
	// 1. 判断必要数据是否存在
	if err = check(info); err != nil {
		return
	}
	// 2. 判断是否存在操作字段
	field := "*"
	if len(info.Field) > 0 {
		field = info.Field[0]
	}
	// 3. 组装基础 SQL
	sql = fmt.Sprintf("SELECT %s FROM `%s`", field, info.Table)
	// 4. 组装别名
	if info.Alias != "" {
		sql += " AS " + info.Alias
	}
	// 5. 组装关联条件
	if len(info.Join) > 0 {
		join := make([]string, 0)
		for _, v := range info.Join {
			join = append(join, fmt.Sprintf(" %s JOIN %s %s ON %s", v.Type, v.Table, v.Alias, v.On))
			if len(v.Args) > 0 {
				values = append(values, v.Args...)
			}
		}
		sql += strings.Join(join, " ")
	}
	// 6. 组装 where 条件
	whereSql, val, err := WhereSql(info)
	if err != nil {
		return sql, values, err
	}
	if whereSql != "" {
		sql += " WHERE " + whereSql
		values = append(values, val...)
	}
	// 7. 组装 Group 条件
	if info.Group != "" {
		sql += " GROUP BY " + info.Group
	}
	// 8. 组装 order 条件
	if info.Order != "" {
		sql += " ORDER BY " + info.Order
	}
	// 9. 组装分页条件
	if info.Rows == 0 {
		info.Rows = 100
	}
	if info.Start == 0 && info.Page != 0 {
		info.Start = (info.Page - 1) * info.Rows
	}
	sql += fmt.Sprintf(" LIMIT %d, %d", info.Start, info.Rows)

	return sql, values, nil
}

// 删除类 SQL
func DeleteSql(info *SqlInfo) (sql string, values []interface{}, err error) {
	// 1. 判断必要数据是否存在
	if err = check(info); err != nil {
		return
	}
	// 2. 组装基础 SQL
	sql = fmt.Sprintf("DELETE FROM `%s`", info.Table)
	// 3. 组装 where 条件
	whereSql, val, err := WhereSql(info)
	if err != nil {
		return sql, values, err
	}
	if whereSql == "" {
		return sql, values, errors.New("sql condition cannot be empty")
	}
	sql += " WHERE " + whereSql
	values = append(values, val...)

	return sql, values, nil
}

// 获取条件 sql
func WhereSql(info *SqlInfo) (sql string, values []interface{}, err error) {
	// 1. 初始化
	sql = ""
	values = make([]interface{}, 0)
	if info.Table == "" {
		return sql, values, errors.New("sql table cannot be empty")
	}
	// 2. 组装基础条件
	if info.Id != 0 {
		sql += fmt.Sprintf("`%s`.`id`=?", info.Table)
		values = append(values, info.Id)
	} else if len(info.Where) != 0 {
		whereSql, v, err := conditionSql(info.Where, "AND")
		if err != nil {
			return sql, values, err
		}
		sql += whereSql
		values = append(values, v...)
	} else {
		// 不存在条件时
		// 增加 1=1 的条件逻辑，用于解决 xorm 关于无条件 QueryInterface() 查询结果统一为 []byte 的问题
		sql = "1=?"
		values = []interface{}{"1"}
		return
	}
	// 3. 组装 AND 连接条件
	if info.And != nil {
		whereSql, v, err := conditionSql(info.And.Condition, info.And.ConnType)
		if err != nil {
			return sql, values, err
		}
		sql += fmt.Sprintf(" AND (%s)", whereSql)
		values = append(values, v...)
	}
	// 4. 组装 OR 连接条件
	if info.Or != nil {
		whereSql, v, err := conditionSql(info.Or.Condition, info.Or.ConnType)
		if err != nil {
			return sql, values, err
		}
		sql += fmt.Sprintf(" OR (%s)", whereSql)
		values = append(values, v...)
	}
	// 5. 组装 AND 连接子条件
	if info.AndChild != nil {
		whereSql, v, err := childConditionSql(info.AndChild.Condition, info.AndChild.ConnType)
		if err != nil {
			return sql, values, err
		}
		sql += fmt.Sprintf(" AND (%s)", whereSql)
		values = append(values, v...)
	}
	// 6. 组装 OR 连接子条件
	if info.OrChild != nil {
		whereSql, v, err := childConditionSql(info.OrChild.Condition, info.OrChild.ConnType)
		if err != nil {
			return sql, values, err
		}
		sql += fmt.Sprintf(" OR (%s)", whereSql)
		values = append(values, v...)
	}

	return sql, values, nil
}

func conditionSql(condition []*Condition, relation string) (sql string, values []interface{}, err error) {
	// 1. 初始化
	values = make([]interface{}, 0)
	where := make([]string, 0)
	// 2. 构造条件
	w, err := buildCondition(condition)
	if err != nil {
		return sql, values, err
	}
	// 3. 遍历获取 sql 信息
	for _, v := range w {
		s, val, err := builder.ToSQL(v)
		if err != nil {
			return sql, values, err
		}
		if s == "" {
			continue
		}
		where = append(where, s)
		values = append(values, val...)
	}
	if len(where) != len(condition) {
		return "", nil, errors.New("condition is abnormal")
	}
	return strings.Join(where, fmt.Sprintf(" %s ", relation)), values, nil
}

func childConditionSql(condition []*ChildCondition, relation string) (sql string, values []interface{}, err error) {
	// 1. 初始化
	values = make([]interface{}, 0)
	where := make([]string, 0)
	// 2. 构造条件
	w, err := buildChildCondition(condition)
	if err != nil {
		return sql, values, err
	}
	// 3. 遍历获取 sql 信息
	for _, v := range w {
		s, val, err := builder.ToSQL(v)
		if err != nil {
			return sql, values, err
		}
		where = append(where, s)
		values = append(values, val...)
	}
	// 4. 返回 sql 及值信息
	return strings.Join(where, fmt.Sprintf(" %s ", relation)), values, nil
}
